package com.chen.dao.user;

import com.chen.dao.BaseDao;
import com.chen.pojo.User;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImp implements UserDao{
    public User getLoginUser(Connection con,String userCode) throws SQLException {
        PreparedStatement ps=null;
        ResultSet rs=null;
        User user=null;
        if (con!=null){
            String sql="select * from smbms_user where userCode = ?";
            Object[] params={userCode};
            rs= BaseDao.execute(con,ps,rs,sql,params);
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            //关闭
            BaseDao.closeResource(null,ps,rs);
        }
        return user;
    }
    public int updatePwd(Connection con,int id,String password) throws SQLException {
        int updateCount = 0;
        if (con!=null){
            PreparedStatement ps=null;
            String sql="update smbms_user set userPassword = ? where id = ?";
            Object[] params = {password,id};
            updateCount = BaseDao.execute(con,ps,sql,params);
            BaseDao.closeResource(con,null,null);
        }
        return updateCount;
    }
    public int getUserCount(Connection con,String username,int userRole) throws SQLException {
        PreparedStatement ps=null;
        ResultSet rs=null;
        int count = 0;
        if (con!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) as 'count' from smbms_user u,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list=new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                list.add("%" + username + "%");
            }
            if (userRole>0){
                sql.append(" and r.id=?");
                list.add(userRole);
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
            if (rs.next()){
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null,ps,rs);
        }
        return count;
    }
    public List<User> getUserList(Connection con,String userName,int userRole,int currentPageNo,int pageSize) throws SQLException{
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<User> userList = new ArrayList<User>();
        if (con!=null){
            StringBuffer sql = new StringBuffer("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list = new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");
            }
            if (userRole>0){
                sql.append(" and r.id=?");
                list.add(userRole);
            }
            sql.append(" order by u.creationDate desc limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(con, ps,rs, sql.toString(), params);
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(user);
            }
            BaseDao.closeResource(null,ps,rs);
        }
        return userList;
    }
}
